package jmind.core.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jmind.base.util.DataUtil;

public class Jdbc {

    private Connection con = null;

    public Jdbc(Connection con) {
        this.con = con;
    }

    public Jdbc(String path, String user, String pwd) throws SQLException {
        this("com.mysql.cj.jdbc.Driver", path, user, pwd);
    }

    /**
     * @param path
     * 			jdbc:mysql://10.1.1.1:3306/dbname?useUnicode=true&characterEncoding=utf-8&autoReconnect=true
     */
    public Jdbc(String driver, String path, String user, String pwd) throws SQLException {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new SQLException("driver not found:" + driver);
        }
        con = DriverManager.getConnection(path, user, pwd);
    }

    private void close() {
        try {
            if (con != null)
                con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 普通查询，list第一个值为存放列名的map
      wbxie
      2013-6-23 
      @param sql
      @return
     */
    public List<Map<Integer, Object>> executeQuery(String sql) {
        List<Map<Integer, Object>> list = new ArrayList<Map<Integer, Object>>();
        try {
            Statement s = con.createStatement();
            ResultSet rs = s.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int cols = rsmd.getColumnCount();
            Map<Integer, Object> map = new HashMap<Integer, Object>();
            for (int j = 1; j <= cols; j++) {
                String colName = rsmd.getColumnName(j);
                map.put(j, colName);
            }
            list.add(map);
            //rs.getFetchSize();
            //rs.getRow();
            while (rs.next()) {
                map = new HashMap<Integer, Object>();
                for (int i = 1; i <= cols; i++) {
                    map.put(i, rs.getObject(i));
                }
                list.add(map);
            }
            rs.close();
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    /**
     * 
     map 键 为列名，值为列值
     * @param sql getColumnName返回的是sql语句中field的原始名字。getColumnLabel是field的SQL AS的值。 
     * @param params 参数
     * @return
     */
    public List<Map<String, Object>> executeQuery(String sql, List<Object> params) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            if (params != null) {
                for (int k = 0; k < params.size(); k++) {
                    ps.setObject(k + 1, params.get(k));
                }
            }
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int cols = rsmd.getColumnCount();
            Map<String, Object> map = null;
            while (rs.next()) {
                map = new HashMap<String, Object>();
                for (int i = 1; i <= cols; i++) {
                    String name = rsmd.getColumnLabel(i); // getColumnName返回的是sql语句中field的原始名字。getColumnLabel是field的SQL AS的值。 
                    if (DataUtil.isEmpty(name))
                        name = rsmd.getColumnName(i);
                    map.put(name, rs.getObject(i));
                }
                list.add(map);
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    /**
     * 检查是否有查询结果
      wbxie
      2013-6-23 
      @param sql
      @return
     */
    public boolean hasResult(String sql) {
        boolean has = false;
        try {
            PreparedStatement ps = con.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                has = true;
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return has;
    }

    /**
     * 查询某一列的值
      wbxie
      2013-6-23 
      @param sql
      @param params
      @return
     */
    @SuppressWarnings("unchecked")
    public <T> List<T> qureyColumn(String sql, List<Object> params) {
        List<T> list = new ArrayList<T>();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            if (params != null) {
                for (int k = 0; k < params.size(); k++) {
                    ps.setObject(k + 1, params.get(k));
                }
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                list.add((T) rs.getObject(1));
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    /**
     * 
      wbxie
      2013-6-23 
      @param sql 
       select count(1) from tableName 
       select id from tableName
      @return
     */
    public int getCounts(String sql) {
        int num = 0;
        List<Map<Integer, Object>> list = executeQuery(sql);
        if (list.size() > 1) {
            Map<Integer, Object> map = list.get(1);
            Object obj = map.get(1);
            if (obj != null)
                num = DataUtil.toInt(obj.toString());
        }
        return num;

    }

    /**
     * 用 Statement
     * @param database
     * @param sql
     * @return
     */
    public int executeSQL(String sql) {
        int i = 0;
        try {
            Statement s = con.createStatement();
            i = s.executeUpdate(sql);
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return i;
    }

    /**
     * 批量执行
      wbxie
      2013-6-23 
      @param sql
      @return
     */
    public int executeBatch(String... sql) {
        int i = -100;
        try {
            Statement s = con.createStatement();
            for (String str : sql) {
                s.addBatch(str);
            }
            i = s.executeBatch()[0];
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return i;
    }

    /**
     * 用 PreparedStatement
     * @param database
     * @param sql
     * @param list 参数
     * @return
     */
    public int executeSQL(String sql, List<Object> list) {
        int i = -100;

        try {
            PreparedStatement ps = con.prepareStatement(sql);
            if (list != null) {
                for (int k = 0; k < list.size(); k++) {
                    ps.setObject(k + 1, list.get(k));
                }
            }
            i = ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return i;
    }

}
